/*********************************************************************************
 *      Copyright:  (C) 2025 LingYun<iot25@lingyun>
 *                  All rights reserved.
 *
 *       Filename:  sqlite.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(24/04/25)
 *         Author:  LingYun <iot25@lingyun>
 *      ChangeLog:  1, Release initial version on "24/04/25 15:14:44"
 *                 
 ********************************************************************************/

#include "sqlite.h"


int init_database(client_ctx_t *ctx)
{
    char *err_msg = NULL;
    int rc;
    
    if (!ctx) {
        return -1;
    }
    
    // 打开或创建数据库
    rc = sqlite3_open(DB_PATH, &ctx->db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(ctx->db));
        sqlite3_close(ctx->db);
        return -1;
    }
    
    // 创建表（如果不存在）
    const char *sql = "CREATE TABLE IF NOT EXISTS temperature_data("
                      "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                      "device_sn TEXT NOT NULL,"
                      "timestamp INTEGER NOT NULL,"
                      "temperature REAL NOT NULL);";
                      
    rc = sqlite3_exec(ctx->db, sql, NULL, 0, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
        return -1;
    }
    
    printf("Database initialized successfully\n");
    return 0;
}



int get_temperature(float *temp)
{
    int fd = -1;
    char buf[128];
    int dv = -1;
    char *str = NULL;
    DIR *opd = NULL;
    char path[64] = "/sys/bus/w1/devices/";
    struct dirent *red = NULL;
    char chip_sn[64] = {0};
    
    if (!temp) {
        return -1;
    }
    
    opd = opendir(path);
    if (!opd) {
        fprintf(stderr, "Can not open directory: %s\n", strerror(errno));
        return -1;
    }
    
    // 查找DS18B20设备
    while ((red = readdir(opd))) {
        if (strstr(red->d_name, "28-")) {
            strncpy(chip_sn, red->d_name, sizeof(chip_sn) - 1);
            break;
        }
    }
    
    if (strlen(chip_sn) == 0) {
        fprintf(stderr, "No DS18B20 device found\n");
        closedir(opd);
        return -1;
    }
    
    strncat(path, chip_sn, sizeof(path) - strlen(path) - 1);
    strncat(path, "/w1_slave", sizeof(path) - strlen(path) - 1);
    printf("Device path: %s\n", path);
    closedir(opd);
    
    fd = open(path, O_RDONLY);
    if (fd < 0) {
        fprintf(stderr, "Can not open device file: %s\n", strerror(errno));
        return -1;
    }
    
    memset(buf, 0, sizeof(buf));
    dv = read(fd, buf, sizeof(buf) - 1);
    if (dv < 0) {
        fprintf(stderr, "Can not read device file: %s\n", strerror(errno));
        close(fd);
        return -1;
    }
    
    str = strstr(buf, "t=");
    if (!str) {
        fprintf(stderr, "Invalid data format\n");
        close(fd);
        return -1;
    }
    
    str += 2;
    *temp = atof(str) / 1000;
    close(fd);
    
    return 0;
}




int report_temperature(client_ctx_t *ctx, float temp)
{
    char message[128];
    time_t current_time;
    int rv;

    if (!ctx || ctx->sockfd < 0) {
        return -1;
    }

    time(&current_time);

    // 格式化上报数据：设备序列号,采样时间,采样温度
    snprintf(message, sizeof(message), "%s,%ld,%.3f", DEVICE_SN, current_time, temp);

    rv = write(ctx->sockfd, message, strlen(message));
    if (rv < 0) {
        fprintf(stderr, "Write to socket[%d] failure: %s\n", ctx->sockfd, strerror(errno));
        return -1;
    }

    printf("Reported data: %s\n", message);
    return 0;
}






int store_temperature(client_ctx_t *ctx, float temp, time_t timestamp)
{
    char sql[256];
    char *err_msg = NULL;
    int rc;

    if (!ctx || !ctx->db) {
        return -1;
    }

    // 构造SQL语句
    snprintf(sql, sizeof(sql),
             "INSERT INTO temperature_data (device_sn, timestamp, temperature) "
             "VALUES ('%s', %ld, %.3f);",
             DEVICE_SN, timestamp, temp);

    // 执行SQL语句
    rc = sqlite3_exec(ctx->db, sql, NULL, 0, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
        return -1;
    }

    printf("Data stored in database\n");
    return 0;
}




// 上报存储在数据库中的数据
int report_stored_data(client_ctx_t *ctx)
{
    sqlite3_stmt *stmt;
    char message[128];
    int rv;
    int count = 0;

    if (!ctx || ctx->sockfd < 0 || !ctx->db) {
        return -1;
    }

    // 查询存储的数据
    const char *sql = "SELECT id, device_sn, timestamp, temperature FROM temperature_data ORDER BY timestamp;";

    rv = sqlite3_prepare_v2(ctx->db, sql, -1, &stmt, NULL);
    if (rv != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(ctx->db));
        return -1;
    }

    // 逐条上报数据
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        int id = sqlite3_column_int(stmt, 0);
        const char *device_sn = (const char *)sqlite3_column_text(stmt, 1);
        long timestamp = sqlite3_column_int64(stmt, 2);
        float temperature = sqlite3_column_double(stmt, 3);

        // 格式化上报数据
        snprintf(message, sizeof(message), "%s,%ld,%.3f", device_sn, timestamp, temperature);

        rv = write(ctx->sockfd, message, strlen(message));
        if (rv < 0) {
            fprintf(stderr, "Write to socket[%d] failure: %s\n", ctx->sockfd, strerror(errno));
            sqlite3_finalize(stmt);
            return -1;
        }

        printf("Reported stored data: %s\n", message);

        // 删除已上报的数据
        char delete_sql[128];
        char *err_msg = NULL;

        snprintf(delete_sql, sizeof(delete_sql), "DELETE FROM temperature_data WHERE id = %d;", id);
        rv = sqlite3_exec(ctx->db, delete_sql, NULL, 0, &err_msg);
        if (rv != SQLITE_OK) {
            fprintf(stderr, "SQL error: %s\n", err_msg);
            sqlite3_free(err_msg);
        }

        count++;

        // 短暂延时，防止服务器端过载
        usleep(100000);  // 100ms
    }

    sqlite3_finalize(stmt);
    printf("Total %d stored records reported and deleted\n", count);

    return count;
}





